﻿using Dapper;
using SP.Domain.IRepository;
using SP.Domain.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SP.EFcore.Repository
{
    public class UserRoleRepository : Repository<UserTable>, IUserRoleRepository
    {
        /// <summary>
        /// 用户角色添加
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="Rid"></param>
        /// <returns></returns>
        public int AddRelation(int Uid, string Rid)
        {
            //开启事务
            using (var conn = _db.GetDbConnection())
            {
                conn.Open();
                //using语句块必须要有
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        //直接进行删除 和 多条添加就行了，上面的不需要
                        var rows = conn.Execute("delete from sp_roleuserrelation where UserId=@UserId", new { UserId = Uid }, trans);
                        string[] ids = Rid.Split(',');
                        foreach (var item in ids)
                        {
                            //懂了吗
                            rows += conn.Execute("insert into sp_roleuserrelation values (null,@RoleId,@UserId)", new { UserId = Uid, RoleId = item }, trans);
                            
                        }
                        //然后提交
                        trans.Commit();
                        return rows;
                    }
                    catch (Exception ex)
                    {
                        //这里回滚
                        trans.Rollback();

                        throw;
                    }
                }
            }
        }
        //删除用户角色关系
        public int DeleteRelation(RoleUserRelation model)
        {
            string sql ="delete from sp_roleuserrelation where UserId=@UserId";
            var list = _db.GetDbConnection().Execute(sql, new { UserId = model.UserId });
            return list;
             
        }

        IEnumerable<dynamic> IUserRoleRepository.GetAll()
        {
            string sql = "select u.Id as UserId,u.*,group_concat(RoleName) as RoleNames from sp_usertable as u left join(select* from(select a.UserId, b.RoleName from sp_roleuserrelation a join sp_roletable b on a.RoleId = b.Id) as r) ur on u.Id = ur.UserId GROUP BY u.Id";
            return _db.GetDbConnection().Query(sql);
        }

    }
}
